USE QUAN_LI_CHUYEN_BAY

--CREATE TABLE KHACHHANG

IF EXISTS (SELECT * FROM DBO.SYSOBJECTS WHERE NAME='KHACHHANG')
BEGIN
	DROP TABLE KHACHHANG
END
GO 
CREATE TABLE KHACHHANG
(
	MAKH VARCHAR(15),
	TEN VARCHAR(15),
	DCHI VARCHAR(50),
	DTHOAI VARCHAR(12)
	PRIMARY KEY(MAKH)
)
EXEC SP_HELP KHACHHANG

--INSERT DATA TO TABLE KHACHHANG

INSERT INTO KHACHHANG VALUES('0009','NGA','223 Nguyen Trai','8932320')
INSERT INTO KHACHHANG VALUES('0101','ANH','567 Tran Phu','8826729')
INSERT INTO KHACHHANG VALUES('0045','THU','285 Le Loi','8932203')
INSERT INTO KHACHHANG VALUES('0012','HA','435 Quang Trung','8933232')
INSERT INTO KHACHHANG VALUES('0238','HUNG','456 Pasteur','9812101')
INSERT INTO KHACHHANG VALUES('0397','THANH','234 Le Van Si','8952943')
INSERT INTO KHACHHANG VALUES('0582','MAI','789 Nguyen Du','')
INSERT INTO KHACHHANG VALUES('0934','MINH','678 Le Lai','')
INSERT INTO KHACHHANG VALUES('0091','HAI','345 Hung Vuong','8893223')
INSERT INTO KHACHHANG VALUES('0314','PHUONG','395 Vo Van Tan','8232320')
INSERT INTO KHACHHANG VALUES('0613','VU','348 CMT8','8343232')
INSERT INTO KHACHHANG VALUES('0586','SON','123 Bach Dang','8556223')
INSERT INTO KHACHHANG VALUES('0422','TIEN','75 Nguyen Thong','8332222')

--CREATE TABLE NHANVIEN

IF EXISTS (SELECT * FROM DBO.SYSOBJECTS WHERE NAME='NHANVIEN')
BEGIN
	DROP TABLE NHANVIEN
END
GO 
CREATE TABLE NHANVIEN
(
	MANV VARCHAR(15),
	TEN VARCHAR(15),
	DCHI VARCHAR(50),
	DTHOAI VARCHAR(12),
	LUONG FLOAT,
	LOAINV BIT
	PRIMARY KEY(MANV)
)

--INSERT DATA TO TABLE NHANVIEN

INSERT INTO NHANVIEN VALUES('1006','CHI','12/6 Nguyen Kiem','8120012','150000',0)
INSERT INTO NHANVIEN VALUES('1005','GIAO','65 Nguyen Thai Son','8324467','500000',0)
INSERT INTO NHANVIEN VALUES('1001','HUONG','8 Dien Bien Phu','8120012','500000',1)
INSERT INTO NHANVIEN VALUES('1002','Phong','1 Ly Thuong Kiet','8308117','450000',1)
INSERT INTO NHANVIEN VALUES('1004','Phuong','351 Lac Long Quan','8308155','250000',0)
INSERT INTO NHANVIEN VALUES('1003','QUANG','78 Truong Dinh','8324461','350000',1)
INSERT INTO NHANVIEN VALUES('1007','TAM','36 Nguyen Van Cu','8458188','500000',0)

--CREATE TABLE LOAIMB

IF EXISTS (SELECT * FROM DBO.SYSOBJECTS WHERE NAME='LOAIMB')
BEGIN
	DROP TABLE LOAIMB
END
GO 
CREATE TABLE LOAIMB
(
	HANGSX VARCHAR(15),
	MALOAI VARCHAR(15)
	PRIMARY KEY(MALOAI)
)

--INSERT DATA TO TABLE LOAIMB

INSERT INTO LOAIMB VALUES('Airbus','A310')
INSERT INTO LOAIMB VALUES('Airbus','A320')
INSERT INTO LOAIMB VALUES('Airbus','A330')
INSERT INTO LOAIMB VALUES('Airbus','A340')
INSERT INTO LOAIMB VALUES('Boeing','B727')
INSERT INTO LOAIMB VALUES('Boeing','B747')
INSERT INTO LOAIMB VALUES('Boeing','B757')
INSERT INTO LOAIMB VALUES('MD','DC10')
INSERT INTO LOAIMB VALUES('MD','DC9')

--CREATE TABLE MAYBAY

IF EXISTS (SELECT * FROM DBO.SYSOBJECTS WHERE NAME='MAYBAY')
BEGIN
	DROP TABLE MAYBAY
END
GO 
CREATE TABLE MAYBAY
(
	SOHIEU INT,
	MALOAI VARCHAR(15),
	PRIMARY KEY(SOHIEU,MALOAI)
)

--INSERT DATA TO TABLE MAYBAY

INSERT INTO MAYBAY VALUES('10','B747')
INSERT INTO MAYBAY VALUES('11','B727')
INSERT INTO MAYBAY VALUES('13','B727')
INSERT INTO MAYBAY VALUES('13','B747')
INSERT INTO MAYBAY VALUES('21','DC10')
INSERT INTO MAYBAY VALUES('21','DC9')
INSERT INTO MAYBAY VALUES('22','B757')
INSERT INTO MAYBAY VALUES('22','DC9')
INSERT INTO MAYBAY VALUES('23','DC9')
INSERT INTO MAYBAY VALUES('24','DC9')
INSERT INTO MAYBAY VALUES('70','A310')
INSERT INTO MAYBAY VALUES('80','A310')
INSERT INTO MAYBAY VALUES('93','B757')


--CREATE TABLE CHUYENBAY

IF EXISTS (SELECT * FROM DBO.SYSOBJECTS WHERE NAME='CHUYENBAY')
BEGIN
	DROP TABLE CHUYENBAY
END
GO 
CREATE TABLE CHUYENBAY
(
	MACB VARCHAR(4),
	SBDI VARCHAR(3),
	SBDEN VARCHAR(3),
	GIODI SMALLDATETIME,
	GIODEN SMALLDATETIME
	PRIMARY KEY(MACB)
)

EXEC SP_HELP CHUYENBAY

--INSERT DATA TO TABLE CHUYENBAY

INSERT INTO CHUYENBAY VALUES('100','SLC','BOS','08:00','17:50')
INSERT INTO CHUYENBAY VALUES('112','DCA','DEN','14:00','18:07')
INSERT INTO CHUYENBAY VALUES('121','STL','SLC','07:00','09:13')
INSERT INTO CHUYENBAY VALUES('122','STL','YYV','08:30','10:19')
INSERT INTO CHUYENBAY VALUES('206','DFW','STL','09:00','11:40')
INSERT INTO CHUYENBAY VALUES('330','JFK','YYV','16:00','18:53')
INSERT INTO CHUYENBAY VALUES('334','ORD','MIA','12:00','14:14')
INSERT INTO CHUYENBAY VALUES('335','MIA','ORD','15:00','17:14')
INSERT INTO CHUYENBAY VALUES('336','ORD','MIA','18:00','20:14')
INSERT INTO CHUYENBAY VALUES('337','MIA','ORD','20:30','23:53')
INSERT INTO CHUYENBAY VALUES('394','DFW','MIA','19:00','21:30')
INSERT INTO CHUYENBAY VALUES('395','MIA','DFW','21:00','23:43')
INSERT INTO CHUYENBAY VALUES('449','CDG','DEN','10:00','19:29')
INSERT INTO CHUYENBAY VALUES('930','YYV','DCA','13:00','16:10')
INSERT INTO CHUYENBAY VALUES('931','DCA','YYV','18:00','17:50')
INSERT INTO CHUYENBAY VALUES('932','DCA','YYV','17:00','18:10')
INSERT INTO CHUYENBAY VALUES('991','BOS','ORD','17:00','18:22')

SELECT * FROM CHUYENBAY

--CREATE TABLE LICHBAY

IF EXISTS (SELECT * FROM DBO.SYSOBJECTS WHERE NAME='LICHBAY')
BEGIN
	DROP TABLE LICHBAY
END
GO 
CREATE TABLE LICHBAY
(
	NGAYDI DATETIME,
	MACB VARCHAR(4),
	SOHIEU INT,
	MALOAI VARCHAR(15)
	PRIMARY KEY(NGAYDI,MACB)
)

--INSERT DATA TO TABLE LICHBAY

INSERT INTO LICHBAY VALUES('11/1/2000','100','80','A310')
INSERT INTO LICHBAY VALUES('11/1/2000','112','21','DC10')
INSERT INTO LICHBAY VALUES('11/1/2000','206','22','DC9')
INSERT INTO LICHBAY VALUES('11/1/2000','334','10','B747')
INSERT INTO LICHBAY VALUES('11/1/2000','395','23','DC9')
INSERT INTO LICHBAY VALUES('11/1/2000','991','22','B757')
INSERT INTO LICHBAY VALUES('11/1/2000','337','10','B747')
INSERT INTO LICHBAY VALUES('10/31/2000','100','11','B727')
INSERT INTO LICHBAY VALUES('10/31/2000','112','11','B727')
INSERT INTO LICHBAY VALUES('10/31/2000','206','13','B727')
INSERT INTO LICHBAY VALUES('10/31/2000','334','10','B747')
INSERT INTO LICHBAY VALUES('10/31/2000','335','10','B747')
INSERT INTO LICHBAY VALUES('10/31/2000','337','24','DC9')
INSERT INTO LICHBAY VALUES('10/31/2000','449','70','A310')

--CREATE TABLE DATCHO

IF EXISTS (SELECT * FROM DBO.SYSOBJECTS WHERE NAME='DATCHO')
BEGIN
	DROP TABLE DATCHO
END
GO 
CREATE TABLE DATCHO
(
	MAKH VARCHAR(15),
	NGAYDI DATETIME,
	MACB VARCHAR(4)
	PRIMARY KEY(MAKH,NGAYDI,MACB)
)

--INSERT DATA TO TABLE DATCHO

INSERT INTO DATCHO VALUES('0009','11/01/2000','100')
INSERT INTO DATCHO VALUES('0009','10/31/2000','449')
INSERT INTO DATCHO VALUES('0045','11/01/2000','991')
INSERT INTO DATCHO VALUES('0012','10/31/2000','206')
INSERT INTO DATCHO VALUES('0238','10/31/2000','334')
INSERT INTO DATCHO VALUES('0582','11/01/2000','991')
INSERT INTO DATCHO VALUES('0091','11/01/2000','100')
INSERT INTO DATCHO VALUES('0314','10/31/2000','449')
INSERT INTO DATCHO VALUES('0613','11/01/2000','100')
INSERT INTO DATCHO VALUES('0586','11/01/2000','991')
INSERT INTO DATCHO VALUES('0586','10/31/2000','100')
INSERT INTO DATCHO VALUES('0422','10/31/2000','449')


--CREATE TABLE KHANANG

IF EXISTS (SELECT * FROM DBO.SYSOBJECTS WHERE NAME='KHANANG')
BEGIN
	DROP TABLE KHANANG
END
GO 
CREATE TABLE KHANANG
(
	MANV VARCHAR(15),
	MALOAI VARCHAR(15)
	PRIMARY KEY(MANV,MALOAI)
)

--INSERT DATA TO TABLE KHANANG

INSERT INTO KHANANG VALUES('1001','B727')
INSERT INTO KHANANG VALUES('1001','B747')
INSERT INTO KHANANG VALUES('1001','DC10')
INSERT INTO KHANANG VALUES('1002','A320')
INSERT INTO KHANANG VALUES('1002','A340')
INSERT INTO KHANANG VALUES('1002','B757')
INSERT INTO KHANANG VALUES('1002','DC9')
INSERT INTO KHANANG VALUES('1003','A310')
INSERT INTO KHANANG VALUES('1003','DC9')

--CREATE TABLE PHANCONG

IF EXISTS (SELECT * FROM DBO.SYSOBJECTS WHERE NAME='PHANCONG')
BEGIN
	DROP TABLE PHANCONG
END
GO 
CREATE TABLE PHANCONG
(
	MANV VARCHAR(15),
	NGAYDI DATETIME,
	MACB VARCHAR(4)
	PRIMARY KEY(MANV,NGAYDI,MACB)
)

--INSERT DATA TO TABLE PHANCONG

INSERT INTO PHANCONG VALUES('1001','11/01/2000','100')
INSERT INTO PHANCONG VALUES('1001','10/31/2000','100')
INSERT INTO PHANCONG VALUES('1002','11/01/2000','100')
INSERT INTO PHANCONG VALUES('1002','10/31/2000','100')
INSERT INTO PHANCONG VALUES('1003','10/31/2000','100')
INSERT INTO PHANCONG VALUES('1003','10/31/2000','337')
INSERT INTO PHANCONG VALUES('1004','10/31/2000','100')
INSERT INTO PHANCONG VALUES('1004','10/31/2000','337')
INSERT INTO PHANCONG VALUES('1005','10/31/2000','337')
INSERT INTO PHANCONG VALUES('1006','11/01/2000','991')
INSERT INTO PHANCONG VALUES('1006','10/31/2000','337')
INSERT INTO PHANCONG VALUES('1007','11/01/2000','112')
INSERT INTO PHANCONG VALUES('1007','11/01/2000','991')
INSERT INTO PHANCONG VALUES('1007','10/31/2000','206')
